<?php
declare (strict_types=1);

namespace app\admin\controller\tally;

use app\admin\controller\CommonController;
use app\common\model\AclUser;
use app\common\model\TallyCustom;
use app\common\model\TallyProgressDay;
use app\common\model\TallyProgressMonth;
use app\common\model\TallyTodo;
use ffhome\common\util\CommonUtil;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use think\facade\Db;

class Stat extends CommonController
{
    public function byCustom()
    {
        $list = CommonUtil::convertToMap($this->query('c.id,a.id', 'c.id,a.id'), 'custom_id');
        $ret = [];
        foreach ($list as $index => $item) {
            $totalTime = 0;
            $totalMoney = 0;
            $number = '';
            $customId = 0;
            $customName = '';
            $ls = [];
            foreach ($item as $it) {
                $number = $it['number'];
                $customId = $it['custom_id'];
                $customName = $it['custom_name'];
                $totalTime += $it['total_time'];
                $money = $it['cost'] * $it['total_time'];
                $totalMoney += $money;
                $ls[] = [
                    'accounting_id' => $it['accounting_id'],
                    'nickname' => $it['nickname'],
                    'total_time' => round($it['total_time'], 2),
                    'cost' => $it['cost'],
                    'money' => round($money, 2),
                ];
            }
            if (count($ls) > 1) {
                $ls[] = [
                    'accounting_id' => 0,
                    'nickname' => lang('common.total'),
                    'total_time' => round($totalTime, 2),
                    'cost' => '',
                    'money' => round($totalMoney, 2),
                ];
            }
            $ret[] = [
                'number' => $number,
                'custom_id' => $customId,
                'custom_name' => $customName,
                'list' => $ls,
            ];
        }
        $this->assign('list', $ret);
        return $this->fetch();
    }

    const STYLE_VERTICAL_CENTER = [
        'alignment' => [
            'vertical' => Alignment::VERTICAL_CENTER, //垂直居中
        ],
    ];

    public function exportByCustom()
    {
        // 初始化
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        //设置头
        $sheet->setCellValue('A1', lang('custom.number'));
        $sheet->setCellValue('B1', lang('custom.name'));
        $sheet->setCellValue('C1', lang('accounting.nickname_alias'));
        $sheet->setCellValue('D1', lang('stat.total_time'));
        $sheet->setCellValue('E1', lang('accounting.cost'));
        $sheet->setCellValue('F1', lang('stat.total_cost'));
        //设置列宽
        $sheet->getColumnDimension('B')->setWidth(20);

        $list = CommonUtil::convertToMap($this->query('c.id,a.id', 'c.id,a.id'), 'custom_id');
        $totalTime = 0;
        $totalMoney = 0;
        $i = 2;
        foreach ($list as $index => $item) {
            $subTime = 0;
            $subMoney = 0;
            $begin = $i;
            if (count($item) > 1) {
                $sheet->mergeCells("A{$begin}:A" . ($i + count($item)));
                $sheet->mergeCells("B{$begin}:B" . ($i + count($item)));
                $sheet->getStyle("A{$begin}:B{$begin}")->applyFromArray(self::STYLE_VERTICAL_CENTER);
            }
            $sheet->setCellValue("A{$begin}", $item[0]['number']);
            $sheet->setCellValue("B{$begin}", $item[0]['custom_name']);
            foreach ($item as $it) {
                $subTime += $it['total_time'];
                $totalTime += $it['total_time'];
                $money = $it['cost'] * $it['total_time'];
                $subMoney += $money;
                $totalMoney += $money;
                $sheet->setCellValue("C{$i}", $it['nickname']);
                $sheet->setCellValue("D{$i}", round($it['total_time'], 2));
                $sheet->setCellValue("E{$i}", $it['cost']);
                $sheet->setCellValue("F{$i}", round($money, 2));
                $i++;
            }
            if (count($item) > 1) {
                $sheet->setCellValue("C{$i}", lang('common.total'));
                $sheet->setCellValue("D{$i}", round($subTime, 2));
                $sheet->setCellValue("F{$i}", round($subMoney, 2));
                $sheet->getStyle("C{$i}:F{$i}")->getFont()->setBold(true);
                $i++;
            }
        }
        $sheet->mergeCells("A{$i}:C{$i}");
        $sheet->setCellValue("A{$i}", lang('common.total'));
        $sheet->setCellValue("D{$i}", round($totalTime, 2));
        $sheet->setCellValue("F{$i}", round($totalMoney, 2));
        $sheet->getStyle("A{$i}:F{$i}")->getFont()->setBold(true);
        $this->exportExcel($spreadsheet);
    }

    public function byAccounting()
    {
        $list = CommonUtil::convertToMap($this->query('a.id,c.id', 'a.id,c.id'), 'accounting_id');
        $ret = [];
        foreach ($list as $index => $item) {
            $totalTime = 0;
            $totalMoney = 0;
            $accountingId = 0;
            $username = '';
            $nickname = '';
            $cost = '';
            $ls = [];
            foreach ($item as $it) {
                $accountingId = $it['accounting_id'];
                $username = $it['username'];
                $nickname = $it['nickname'];
                $cost = $it['cost'];
                $totalTime += $it['total_time'];
                $money = $it['cost'] * $it['total_time'];
                $totalMoney += $money;
                $ls[] = [
                    'custom_id' => $it['custom_id'],
                    'custom_name' => $it['custom_name'],
                    'total_time' => round($it['total_time'], 2),
                    'money' => round($money, 2),
                ];
            }
            if (count($ls) > 1) {
                $ls[] = [
                    'custom_id' => 0,
                    'custom_name' => lang('common.total'),
                    'total_time' => round($totalTime, 2),
                    'money' => round($totalMoney, 2),
                ];
            }
            $ret[] = [
                'accounting_id' => $accountingId,
                'username' => $username,
                'nickname' => $nickname,
                'cost' => $cost,
                'money' => round($totalMoney, 2),
                'list' => $ls,
            ];
        }
        $this->assign('list', $ret);
        return $this->fetch();
    }

    public function exportByAccounting()
    {
        // 初始化
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        //设置头
        $sheet->setCellValue('A1', lang('accounting.username_alias'));
        $sheet->setCellValue('B1', lang('accounting.name'));
        $sheet->setCellValue('C1', lang('accounting.cost'));
        $sheet->setCellValue('D1', lang('custom.name'));
        $sheet->setCellValue('E1', lang('stat.total_time'));
        $sheet->setCellValue('F1', lang('accounting.total_cost'));
        //设置列宽
        $sheet->getColumnDimension('D')->setWidth(20);

        $list = CommonUtil::convertToMap($this->query('a.id,c.id', 'a.id,c.id'), 'accounting_id');
        $totalTime = 0;
        $totalMoney = 0;
        $i = 2;
        foreach ($list as $index => $item) {
            $subTime = 0;
            $subMoney = 0;
            $begin = $i;
            if (count($item) > 1) {
                $sheet->mergeCells("A{$begin}:A" . ($i + count($item)));
                $sheet->mergeCells("B{$begin}:B" . ($i + count($item)));
                $sheet->mergeCells("C{$begin}:C" . ($i + count($item)));
                $sheet->mergeCells("F{$begin}:F" . ($i + count($item)));
                $sheet->getStyle("A{$begin}:C{$begin}")->applyFromArray(self::STYLE_VERTICAL_CENTER);
                $sheet->getStyle("F{$begin}")->applyFromArray(self::STYLE_VERTICAL_CENTER);
            }
            $sheet->setCellValue("A{$begin}", $item[0]['username']);
            $sheet->setCellValue("B{$begin}", $item[0]['nickname']);
            $sheet->setCellValue("C{$begin}", $item[0]['cost']);
            foreach ($item as $it) {
                $subTime += $it['total_time'];
                $totalTime += $it['total_time'];
                $money = $it['cost'] * $it['total_time'];
                $subMoney += $money;
                $totalMoney += $money;
                $sheet->setCellValue("D{$i}", $it['custom_name']);
                $sheet->setCellValue("E{$i}", round($it['total_time'], 2));
                $i++;
            }
            if (count($item) > 1) {
                $sheet->setCellValue("D{$i}", lang('common.total'));
                $sheet->setCellValue("E{$i}", round($subTime, 2));
                $sheet->getStyle("D{$i}:E{$i}")->getFont()->setBold(true);
                $i++;
            }
            $sheet->setCellValue("F{$begin}", round($subMoney, 2));
        }
        $sheet->mergeCells("A{$i}:D{$i}");
        $sheet->setCellValue("A{$i}", lang('common.total'));
        $sheet->setCellValue("E{$i}", round($totalTime, 2));
        $sheet->setCellValue("F{$i}", round($totalMoney, 2));
        $sheet->getStyle("A{$i}:F{$i}")->getFont()->setBold(true);
        $this->exportExcel($spreadsheet);
    }

    private function query($group, $order)
    {
        $param = $this->request->param();
        if (!isset($param['d_progress_date_ge'])) {
            $param['d_progress_date_ge'] = date('Y-m-01');
        }
        $where = $this->buildWhere($param);
        if (empty($param['m_custom_id_eq'])) {
            $param['m_custom_id_eq'] = 0;
        }
        if (empty($param['m_accounting_id_eq'])) {
            $param['m_accounting_id_eq'] = 0;
        }
        $this->assign('param', $param);
        $where[] = ['c.id', 'exp', Db::raw('is not null')];

        return Db::name(TallyTodo::MODEL_NAME)->alias('t')
            ->leftJoin(TallyProgressDay::MODEL_NAME . ' d', 't.day_id=d.id')
            ->leftJoin(TallyProgressMonth::MODEL_NAME . ' m', 't.month_id=m.id')
            ->leftJoin(TallyCustom::MODEL_NAME . ' c', 'm.custom_id=c.id')
            ->leftJoin(AclUser::MODEL_NAME . ' a', 'd.accounting_id=a.id')
            ->field('m.custom_id,c.number,c.name custom_name,d.accounting_id,a.username,a.nickname,a.cost,sum(t.actual_time) total_time')
            ->where($where)->group($group)->order($order)->select()->toArray();
    }
}
